﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace JobUsersSyncTest
{
    public static class DB1CUser
    {
        public static Exception GetDateBirth(ref string parStr, String parID1C)
        {
            #region Members
            Exception ret = null;
            String mDate = "";
            #endregion Members
            #region sql
            string sqlStr =
@"
Select 
convert(char(10), f._Fld1165,121) as DnRo
, convert(char(10), Max (r._Period),121) As Period 
From _InfoRg5080 As r 
    Left Join _Reference84 As s Inner Join _Reference101 As f 
        On s._Fld993RRef = f._IDRRef On r._Fld5081RRef = s._IDRRef 
Where 
    r._Fld5096RRef = 0xa4854b8d2b756bb24cf0d8eacc60b5d6 
    And s._Fld998RRef = 0x850666574703fd8a4f18c2299ebfad6a 
    And s._Fld994 = 1 
    AND f._Code = ? 
Group By f._Code , f._Fld1165
";
            #endregion sql
            #region Get Data
            try
            {
                using (OleDbConnection conn = new OleDbConnection())
                {
                    conn.ConnectionString = KoEnvironment.GetAppSetting("1CConnect");
                    conn.Open();

                    using (OleDbCommand cmd = new OleDbCommand(sqlStr, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("parID1C", OleDbType.VarChar).Value = parID1C;
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                if (reader[0] == DBNull.Value)
                                {
                                    ret = new Exception("Error - 1C::GetDateBirth()");
                                    break;
                                }
                                else
                                {
                                    mDate = reader[0].ToString();
                                    parStr = mDate;
                                    break;
                                }
                            }
                        }
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                } // using (OleDbConnection conn = new OleDbConnection())
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            #endregion Get Data
            return ret;
        }
        public static Exception GetDateArb(ref string parStr, String parID1C)
        {
            #region Members
            Exception ret = null;
            String mDate = "";
            #endregion Members
            #region sql
            string sqlStr =
@"
Select 
      convert(char(10), f._Fld1165,121) as DnRo
    , convert(char(10), Max (r._Period),121) As Period 
From 
    _InfoRg5080 As r 
        Left Join _Reference84 As s Inner Join _Reference101 As f 
            On s._Fld993RRef = f._IDRRef On r._Fld5081RRef = s._IDRRef 
Where 
    r._Fld5096RRef = 0xa4854b8d2b756bb24cf0d8eacc60b5d6 
    And s._Fld998RRef = 0x850666574703fd8a4f18c2299ebfad6a 
    And s._Fld994 = 1 AND f._Code = ? 
Group By f._Code , f._Fld1165
";
            #endregion sql
            #region Get Data
            try
            {
                using (OleDbConnection conn = new OleDbConnection())
                {
                    conn.ConnectionString = KoEnvironment.GetAppSetting("1CConnect");
                    conn.Open();

                    using (OleDbCommand cmd = new OleDbCommand(sqlStr, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("parID1C", OleDbType.VarChar).Value = parID1C;
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                if (reader[1] == DBNull.Value)
                                {
                                    ret = new Exception("Error - 1C::GetDateArb()");
                                    break;
                                }
                                else
                                {
                                    mDate = reader[1].ToString();
                                    parStr = mDate;
                                    break;
                                }
                            }
                        }
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                } // using (OleDbConnection conn = new OleDbConnection())
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            #endregion Get Data
            return ret;
        }
    }
}
